using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.IO;
using System.Text;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using LAFDC.DataAccess;
using LAFDC.Logging;

namespace LAFDC.Web.UI
{

    public partial class Prelevement : System.Web.UI.Page
    {
        public static readonly string source = "Prelevement.aspx";
        private string month = string.Empty;
        private string year = string.Empty;

        protected void Page_Load(object sender, EventArgs e)
        {
            string query_param = this.Request.QueryString.Get("input");
            string sql_query = string.Empty;

            year = DateTime.Now.Year.ToString();

            if ((query_param == null) || (query_param.Length == 0))
            {
            }
            else
            {
                string[] r = ParseInput(query_param);
                sql_query = GenerateSQL(r);

                Random x = new Random();
                string fpart = string.Empty;
                // fpart = x.Next(1000000, 9999999).ToString();

                string logFile = "prelev" + fpart + ".txt";
                string logDir = "C:\\program files\\LAFDC\\LAFDCWeb\\Logging";
                string logPath = logDir + "\\" + logFile;

                if (!Directory.Exists(logDir))
                {
                    Directory.CreateDirectory(logDir);
                }

                // Check if the file exist before trying to write events there;
                if (!File.Exists(logPath))
                {
                    using (StreamWriter sw = new StreamWriter(logPath))
                    {
                        sw.Close();
                    }
                }

                using (StreamWriter sw = new StreamWriter(logPath, false, Encoding.Default))
                {
                    sw.Write(ExecuteSql(sql_query));
                    sw.Close();
                }

                Response.Clear();
                Response.AddHeader("content-disposition", "attachment;filename=FCPREA" + year.Remove(0, 2) + ".txt");
                Response.ContentType = "text/plain";
                Response.WriteFile(logPath);
                Response.End();

            }
        }

        private string[] ParseInput(string json)
        {
            // We are parsing this type of string (JSON):
            // [["01","0001234"],["0003409","10"]]

            StringBuilder debug = new StringBuilder();

            // Count elements in the array
            int count_base = json.Length;
            string temp = json.Replace("[", "");
            temp = temp.Replace("]", "");

            int count_left = temp.Length;
            int array_element = (count_base - count_left - 2) / 2;

            string[] s_array = new string[array_element];

            int c_depth = 99; // Maximum number of chars in the char array 
            char[] c_array = new char[c_depth];

            // Initialise the char array to contain spaces (for an easy trim afterward)
            for (int z = 0; z < c_depth; z++)
            {
                c_array[z] = ' ';
            }
            int currentDepth = 0;
            int i = 0; int j = 0;
            char prev_c = new char();

            foreach (char c in json.ToCharArray())
            {
                if (currentDepth == 0 && c == '[')
                {
                    currentDepth++;
                }
                if (currentDepth > 0)
                {
                    if (prev_c == '[' && c == '"')
                    {
                        currentDepth++;
                        c_array[i] = c;
                    }
                    else if (prev_c == '"' && c == ']')
                    {
                        currentDepth--;
                        s_array[j] = new string(c_array).TrimStart(",".ToCharArray()).TrimEnd();

                        i = 0;
                        j++;
                    }
                    else
                    {
                        // All other cases should be words to add to the c_array?
                        if (c != '[' && c != ']' && c != '"')
                        {
                            c_array[i] = c;
                            i++;
                        }
                    }
                }
                if (currentDepth == 1 && c == ']')
                {
                    //debug.AppendLine("Last char in the input string is:" + c.ToString() + "<br/>");
                }
                if (currentDepth > 1)
                {
                }
                prev_c = c;
            }

            return s_array;
        }

        private string GenerateSQL(string[] s_array)
        {
            int i = 0;
            string[] a = new string[2];
            StringBuilder sql = new StringBuilder();
            sql.Append("declare @ident_fc as char(8)\n\tset @ident_fc = '96049184'");

            foreach (string entry in s_array)
            {
                if (i == 0)
                {
                    // Split the string to extract 'month' and 'cotiz_std'
                    a = entry.Split(",".ToCharArray());
                    sql.Append("declare @month as char(2)\n\tset @month = '" + a[0] + "'\n");
                    this.month = a[0];
                    sql.Append("declare @cotiz_std as char(7)\n\tset @cotiz_std = '" + a[1] + "' ");
                    sql.Append("select adh.prev_id + @month + @ident_fc + '-' \n");
                    // sql.Append("case AD ");
                }
                else
                {
                    if (i == 1)
                    {
                        // First occurence
                        sql.Append("+ case AD ");
                    }
                    a = entry.Split(",".ToCharArray());
                    sql.Append("\t\t when '" + a[1] + "' then '" + a[0] + "'");
                }
                i++;
            }

            if (i > 1)
            {
                sql.Append("else @cotiz_std end\n");
            }
            sql.Append("\t as prelevement\n");
            sql.Append("from adherent adh \n");
            sql.Append("join divers div \n");
            sql.Append("on adh.prev_id = div.prev_id \n");
            sql.Append("where div.indice_retraite > 0 \n");
            sql.Append("and adh.retenu = 'OUI' \n");
            sql.Append("and adh.prev_id !='' \n");
            sql.Append("order by prelevement");
            return sql.ToString();
        }

        private string ExecuteSql(string sql)
        {
            ConnectionManager cn = new ConnectionManager();
            SqlConnection conn = cn.GetSqlConnection();
            SqlCommand tsql = conn.CreateCommand();

            tsql.CommandType = CommandType.Text;
            tsql.CommandText = sql;

            FDCLogger.LogEvent("Preparing to execute SQL statement: " + sql, source, 1);

            conn.Open();
            int c = 0;
            StringBuilder file = new StringBuilder();

            using (SqlDataReader r = tsql.ExecuteReader())
            {
                while (r.Read())
                {
                    c++; //Count of results to be reported
                    file.AppendLine(r.GetString(0));
                }
            }
            conn.Close();
            string result_count = c.ToString();
            switch (result_count.Length)
            {
                case (1):
                    result_count = "0000000" + result_count;
                    break;
                case (2):
                    result_count = "000000" + result_count;
                    break;
                case (3):
                    result_count = "00000" + result_count;
                    break;
                case (4):
                    result_count = "0000" + result_count;
                    break;
                case (5):
                    result_count = "000" + result_count;
                    break;
                case (6):
                    result_count = "00" + result_count;
                    break;
                case (7):
                    result_count = "0" + result_count;
                    break;
            }
            file.AppendLine("F" + month + "2008" + result_count);
            return file.ToString();

        }
    }
}